package com.qf.spring005.service.impl;

import com.qf.spring005.entity.User;
import com.qf.spring005.service.UserService;

import java.sql.*;
import java.util.List;

public class UserServiceJDBCImpl implements UserService {

    @Override
    public List<User> getAll() {
        return null;
    }

    @Override
    public void transfer(Integer fromId, Integer toId, Integer amount) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection conn =null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "root");

            // 如何开启事务？
            conn.setAutoCommit(false);  // 关闭当前连接的自动提交模式，相当于sqlyog中写 START TRANSACTION;

            // 查询fromId中原来的账户金额
            String fromSql = "select amount from t_users where id=?";

            final PreparedStatement preparedStatement = conn.prepareStatement(fromSql);
            preparedStatement.setInt(1,fromId);
            final ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.next();
            final int fromAmount = resultSet.getInt("amount");

            // 计算 from账户中最后应该更新金额
            final int fromUpdateAmount = fromAmount - amount;

            // 更新 from账户的金额
            String fromUpdateSql = "update t_users set amount=? where id=?";
            final PreparedStatement fromUpdateStatement = conn.prepareStatement(fromUpdateSql);
            fromUpdateStatement.setInt(1,fromUpdateAmount);
            fromUpdateStatement.setInt(2,fromId);
            fromUpdateStatement.execute(); //执行

            // 其他的业务逻辑
         // int a = 1/0;

            // 查询toId中原来的账户金额
            String toSql = "select amount from t_users where id=?";
            final PreparedStatement toStatment = conn.prepareStatement(toSql);
            toStatment.setInt(1,toId);
            final ResultSet toResultSet = toStatment.executeQuery();
            toResultSet.next();
            final int toAmount = toResultSet.getInt("amount");

            // 计算to账户应该更新的金额
            final int toUpdateAmount = toAmount + amount;

            String toUpdateSql = "update t_users set amount=? where id=?";
            final PreparedStatement toUpdateStatement = conn.prepareStatement(toUpdateSql);
            toUpdateStatement.setInt(1,toUpdateAmount);
            toUpdateStatement.setInt(2,toId);
            toUpdateStatement.execute();//执行

            // 确认转账操作
            conn.commit();


        } catch (Exception e) {
            e.printStackTrace();
            // 回滚数据库之前在该事务中的所有操作
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

    }
}

